package cn.emove.core.util;


import cn.emove.core.common.constant.factory.ConstantFactory;
import cn.emove.core.common.constant.state.TicketStatus;
import cn.emove.modular.system.model.TicketModel;
import jxl.Cell;
import jxl.Workbook;
import jxl.format.*;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * DESC: 读取模板导出Excel报表<BR>
 * AUTHOR: GuoMM ( Goma ) oma1989@yeah.net<BR>
 * VERSION: 1.0<BR>
 * DATE:2011-11-7 14:23:51<BR>
 */
public class ExcelUtil {
    private String fileUrl;
    /**
     *
     * @param fileUrl 模板路径<br>
     * DESC:创建实例，并初始化模板<br>
     */
    public ExcelUtil(String fileUrl){
        this.fileUrl = fileUrl;
    }


    /**
     *
     * @param rows 模板内配置所占行数<br>
     * @param cols 模板内配置所占列数<br>
     * @param result 要导出报表的数据集<br>
     * @return excel报表<br>
     *
     */
    public InputStream export(int rows, int cols, List<?> result){
        try{
            File file = new File(fileUrl);
            FileInputStream fint = new FileInputStream(file);
            POIFSFileSystem poiFileSystem = new POIFSFileSystem(fint);
            HSSFWorkbook wb = new HSSFWorkbook(poiFileSystem);
            HSSFSheet sheet = wb.getSheetAt(0);
            GetProperty gp = new GetProperty();
            HSSFRow namesRow = sheet.getRow(rows-2);
            HSSFRow typeRow = sheet.getRow(rows-1);

            for(int i=0;i<result.size();i++){
                HSSFRow row = sheet.getRow(rows+i);
                if(row==null){
                    row = sheet.createRow(rows+i);
                }
                for(int j=0;j<cols;j++){
                    HSSFCell cell = row.getCell((short)j);
                    if (cell == null){
                        cell = row.createCell((short)j);
                    }
                    //获得方法名
                    HSSFCell nameCell = namesRow.getCell((short)j);
                    HSSFCell typeCell = typeRow.getCell((short)j);
                    String name = nameCell.getRichStringCellValue().getString();
                    String type = typeCell.getRichStringCellValue().getString();
                    //java反射机制获得方法值
                    Object obj = gp.getProperty(name, result.get(i).getClass(), result.get(i));
                    //写入Excel中
                    if("Number".equals(type)){
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        if(obj!=null&&!"".equals(obj)){
                            cell.setCellValue(Double.valueOf(String.valueOf(obj)));
                        }else{
                            cell.setCellValue(0);
                        }
                    }else{
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        if(obj==null){
                            cell.setCellValue(new HSSFRichTextString(""));
                        }else{
                            cell.setCellValue(new HSSFRichTextString(String.valueOf(obj)));
                        }
                    }
                }
            }

            sheet.shiftRows(rows, result.size()+rows, -2);
            System.out.println("WRITE EXCEL REPORT IS OK..");
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            wb.write(os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            return is;

        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 导出Excel（样品）
     * @param sheetName sheet名称
     * @param title 标题
     * @param wb HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName, String []title, List<TicketModel> list, HSSFWorkbook wb){
        HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        sheet.setDefaultColumnWidth(30);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 1150);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            //sheet.setColumnWidth(i, (short) (24 * 256));
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            HSSFFont font = wb.createFont();
            font.setFontName("黑体");
            font.setFontHeightInPoints((short) 15);//设置字体大小
            style.setFont(font);
            cell.setCellStyle(style);
        }
        try {
            //创建内容
            HSSFCellStyle styleCon = wb.createCellStyle();
            styleCon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
            styleCon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            styleCon.setWrapText(true);
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

            for(int i=0;i<list.size();i++){
                row = sheet.createRow(i + 1);
                row.setHeight((short) 550);
                row.setHeightInPoints(100);
                TicketModel t= list.get(i);
                //获取图片路径并且处理
                /*String endImages = t.getEndImages();
                List<String> images = new ArrayList<>();
                if(endImages!=null&&!"".equals(endImages)){
                    String[] eis = endImages.split(",");
                    for (int j = 0; j < eis.length; j++){
                        String image = "/www" + eis[j].substring(eis[j].indexOf("8008") + 4);
                        images.add(image);
                    }

                }*/
                List<String> images = new ArrayList<>();
                images.add("C:\\app\\1.jpg");
                images.add("C:\\app\\2.jpg");
               if(images.size()>0){
                   for(int j = 0; j < images.size(); j++){
                       row.setHeight((short) 550);
                       row.setHeightInPoints(100);
                       ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                       String baseImage = images.get(j);
                       BufferedImage bufferImg = ImageIO.read(new File(baseImage));
                       ImageIO.write(bufferImg, "jpg", byteArrayOut);
                       HSSFClientAnchor anchor = new HSSFClientAnchor(30, 15, 0, 0,   (short) (j+8), i+1, (short) (j+9), i+2);
                       anchor.setAnchorType(3);
                       patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
                               .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

                       bufferImg.flush();
                   }
               }


                cell = row.createCell(0);cell.setCellValue(t.getOrderNo());
                cell.setCellStyle(styleCon);
                cell = row.createCell(1);cell.setCellValue(t.getContactName());
                cell.setCellStyle(styleCon);
                cell = row.createCell(2);cell.setCellValue(t.getDescribes()+"\t");
                cell.setCellStyle(styleCon);
                cell = row.createCell(3);cell.setCellValue(ConstantFactory.me().getOperatorName(t.getOperatorId()));
                cell.setCellStyle(styleCon);
                cell = row.createCell(4);cell.setCellValue(t.getRepairType());
                cell.setCellStyle(styleCon);
                cell = row.createCell(5);cell.setCellValue(t.getCreateTime());
                cell.setCellStyle(styleCon);
                cell = row.createCell(6);cell.setCellValue(TicketStatus.getDescription(t.getType()));
                cell.setCellStyle(styleCon);
                cell = row.createCell(7);cell.setCellValue(t.getUpdateTime());
                cell.setCellStyle(styleCon);
            }
            return wb;
        } catch (Exception e) {
            // TODO: handle exception
            System.err.println(e.getMessage());
        }
        return wb;
    }

    /**
     * jxl导出excel
     * @param sheetName
     * @param title
     * @param list
     * @param wb
     * @return
     */
    public static WritableWorkbook getWorkbook(String sheetName, String []title, List<TicketModel> list, HSSFWorkbook wb) throws Exception {
        WritableWorkbook workbook = Workbook.createWorkbook(new File("/www/员工工单.xls"));
        //创建Excel电子薄;
        WritableSheet sheet = workbook.createSheet("员工工单", 0);
        Cell[] cells = sheet.getRow(0);
        //创建标题
        for(int i=0;i<title.length;i++){
            sheet.setColumnView(i, 20);//列宽

            for(int j=0;j<=list.size();j++){

                if(j==0){
                    Label label1 = new Label(i, j, title[i]);
                    sheet.addCell(label1);
                }else{
                    TicketModel ticket = list.get(j-1);
                    if(i==9){
                        //调用图片插入函数
                        String images = ticket.getSubmitImages();

                        if(images!=null&&!"".equals(images)){
                            String[] ims = images.split(",");
                            StringBuffer sb = new StringBuffer();
                            for (String is : ims ){
                                String image = "/www" + is.substring(is.indexOf("8008") + 4);
                                sb.append(image+",");
                            }
                            String[] filePaths = sb.substring(0, sb.length() - 1).split(",");
                            addPictureToExcel(sheet,filePaths,j,i);

                        }
                        /*String[] filePaths = new String[3];
                        filePaths[0] = "/app/5.jpg";
                        filePaths[1] = "/app/6.jpg";
                        filePaths[2] = "/app/7.jpg";
                        addPictureToExcel(sheet,filePaths,j,i);*/

                    }else if(i==0){
                        Label label1 = new Label(i, j,ticket.getOrderNo());
                        sheet.addCell(label1);
                    }else if(i==1){
                        Label label1 = new Label(i, j,ticket.getContactName());
                        sheet.addCell(label1);
                    }else if(i==2){
                        if(ticket.getContactPhone()!=null&&!"".equals(ticket.getContactPhone())){
                            Label label1 = new Label(i, j,ticket.getContactPhone().replaceAll("(\\d{3})\\d{4}(\\d{4})","$1****$2"));
                            sheet.addCell(label1);
                        }
                    }else if(i==3){
                        Label label1 = new Label(i, j,ticket.getDescribes());
                        sheet.addCell(label1);
                    }else if(i==4){
                        Label label1 = new Label(i, j,ConstantFactory.me().getOperatorName(ticket.getOperatorId()));
                        sheet.addCell(label1);
                    }else if(i==5){
                        Label label1 = new Label(i, j,ticket.getRepairType());
                        sheet.addCell(label1);
                    }else if(i==6){
                        Label label1 = new Label(i, j,ticket.getCreateTime());
                        sheet.addCell(label1);
                    }else if(i==7){
                        Label label1 = new Label(i, j,TicketStatus.getDescription(ticket.getType()));
                        sheet.addCell(label1);
                    }else if(i==8){
                        Label label1 = new Label(i, j,ticket.getUpdateTime());
                        sheet.addCell(label1);
                    }if(i==10){

                        //调用图片插入函数
                        String images = ticket.getEndImages();

                        if(images!=null&&!"".equals(images)){
                            String[] ims = images.split(",");
                            StringBuffer sb = new StringBuffer();
                            for (String is : ims ){
                                String image = "/www" + is.substring(is.indexOf("8008") + 4);
                                sb.append(image+",");
                            }
                            String[] filePaths = sb.substring(0, sb.length() - 1).split(",");
                            addPictureToExcel(sheet,filePaths,j,i);

                        }
                    }
                }

            }

        }
        return workbook;
    }

    /**
     * 无法处理工单导出
     * @param sheetName
     * @param title
     * @param list
     * @param wb
     * @return
     */
    public static WritableWorkbook getMishandleWorkbook(String sheetName, String[] title, List<TicketModel> list, HSSFWorkbook wb) throws Exception {
        WritableWorkbook workbook = Workbook.createWorkbook(new File("/www/无法处理工单.xls"));
        //创建Excel电子薄;
        WritableSheet sheet = workbook.createSheet("无法处理工单", 0);
        //创建标题
        for(int i=0;i<title.length;i++){
            sheet.setColumnView(i, 20);//列宽

            for(int j=0;j<=list.size();j++){

                if(j==0){
                    Label label1 = new Label(i, j, title[i]);
                    sheet.addCell(label1);
                }else{
                    TicketModel ticket = list.get(j-1);
                    if(i==8){
                        //调用图片插入函数
                        String images = ticket.getSubmitImages();

                        if(images!=null&&!"".equals(images)){
                            String[] ims = images.split(",");
                            StringBuffer sb = new StringBuffer();
                            for (String is : ims ){
                                String image = "/www" + is.substring(is.indexOf("8008") + 4);
                                sb.append(image+",");
                            }
                            String[] filePaths = sb.substring(0, sb.length() - 1).split(",");
                            addPictureToExcel(sheet,filePaths,j,i);

                        }
                        /*String[] filePaths = new String[3];
                        filePaths[0] = "/app/5.jpg";
                        filePaths[1] = "/app/6.jpg";
                        filePaths[2] = "/app/7.jpg";
                        addPictureToExcel(sheet,filePaths,j,i);*/

                    }else if(i==0){
                        Label label1 = new Label(i, j,ticket.getOrderNo());
                        sheet.addCell(label1);
                    }else if(i==1){
                        Label label1 = new Label(i, j,ticket.getContactName());
                        sheet.addCell(label1);
                    }else if(i==2){
                        Label label1 = new Label(i, j,ticket.getDescribes());
                        sheet.addCell(label1);
                    }else if(i==3){
                        Label label1 = new Label(i, j,ticket.getChangeOrderReason());
                        sheet.addCell(label1);
                    }else if(i==4){
                        Label label1 = new Label(i, j,ConstantFactory.me().getOperatorName(ticket.getOperatorId()));
                        sheet.addCell(label1);
                    }else if(i==5){
                        Label label1 = new Label(i, j,ticket.getCreateTime());
                        sheet.addCell(label1);
                    }else if(i==6){
                        Label label1 = new Label(i, j,TicketStatus.getDescription(ticket.getType()));
                        sheet.addCell(label1);
                    }else if(i==7){
                        Label label1 = new Label(i, j,ticket.getUpdateTime());
                        sheet.addCell(label1);
                    }else if(i==9){

                        //调用图片插入函数
                        String images = ticket.getEndImages();

                        if(images!=null&&!"".equals(images)){
                            String[] ims = images.split(",");
                            StringBuffer sb = new StringBuffer();
                            for (String is : ims ){
                                String image = "/www" + is.substring(is.indexOf("8008") + 4);
                                sb.append(image+",");
                            }
                            String[] filePaths = sb.substring(0, sb.length() - 1).split(",");
                            addPictureToExcel(sheet,filePaths,j,i);

                        }
                    }else if(i==10){

                        //调用图片插入函数
                        String images = ticket.getNohandleImages();

                        if(images!=null&&!"".equals(images)){
                            String[] ims = images.split(",");
                            StringBuffer sb = new StringBuffer();
                            for (String is : ims ){
                                String image = "/www" + is.substring(is.indexOf("8008") + 4);
                                sb.append(image+",");
                            }
                            String[] filePaths = sb.substring(0, sb.length() - 1).split(",");
                            addPictureToExcel(sheet,filePaths,j,i);

                        }
                    }

                }

            }

        }
        return workbook;
    }

    /**
     *
     * @Title: addPictureToExcel
     * @Description: TODO(将多个图片按实际大小，插入同一个单元格,最后一张图如果高度超过了单元格，则压缩高度使之在单元格内)
     * @date 2016年12月16日 下午6:13:52
     * @param @param picSheet
     * @param @param pictureFilePaths
     * @param @param cellRow
     * @param @param cellCol
     * @param @throws Exception 设定文件
     * @return void 返回类型
     * @throws
     */
    private static void addPictureToExcel(WritableSheet picSheet, String[] pictureFilePaths, double cellRow, double cellCol)
            throws Exception {

        final double cellSpace = 0.02;//图片之间的间隔 占比

        double picWidthMax = 0;
        double picHeightSum =0;//空出图片 离上下边框的距离
        ImgFile[] imgFiles = new ImgFile[pictureFilePaths.length];

        for (int i=0;i<pictureFilePaths.length;i++) {
            try {
                ImgFile imgFile = new ImgFile();
                File imageFile = new File(pictureFilePaths[i]);
                // 读入图片
                BufferedImage picImage = ImageIO.read(imageFile);
                ByteArrayOutputStream pngByteArray = new ByteArrayOutputStream();
                //将其他图片格式写成png的形式
                ImageIO.write(picImage, "jpg", pngByteArray);
                imgFile.setPngByteArray(pngByteArray);
                // 取得图片的像素高度，宽度
                double picWidth = 18;
                //具体的实验值，原理不清楚。
                double picHeight = 3000; //具体的实验值，原理不清楚。

                imgFile.setHeigth(picHeight);
                imgFile.setWidth(picWidth);
                //汇总

                picWidthMax = picWidthMax+picWidth;

                if(picHeight>picHeightSum){
                    picHeightSum = picHeight;
                }

                imgFiles[i] = imgFile;
            }catch (Exception e){
                System.out.println(e);
                continue;
            }

        }

        WritableFont font = new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE, Colour.RED);
        WritableCellFormat cellFormat = new WritableCellFormat(font);
        //设置背景颜色;
        cellFormat.setBackground(Colour.WHITE);
        //设置边框;
        cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
        //设置自动换行;
        cellFormat.setWrap(false);
        //设置文字居中对齐方式;
        cellFormat.setAlignment(Alignment.CENTRE);
        //设置垂直居中;
        cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

        Label imageLabel = new Label((int)cellCol, (int)cellRow, "",cellFormat);
        picSheet.addCell(imageLabel);

        //设置单元格宽高
        picSheet.setColumnView((int)cellCol, (int)picWidthMax+10);//列宽
        picSheet.setRowView((int)cellRow, (int)picHeightSum);//行高

        double widthStart = cellSpace;//开始宽度
        double heightStart = cellSpace;//开始高度
        //插入图片
        for (ImgFile imgFile0: imgFiles) {
            try {
                double heigthFact = imgFile0.getHeigth()/picHeightSum;//实际高度
                double widthFact = imgFile0.getWidth()/picWidthMax;
                //图片高度压缩了cellSpace+moreHeight,目的是为了该图片高度不超出单元格
                if (widthStart + widthFact >= 1) {
                    double moreWidth = widthStart + widthFact - 1.00;
                    widthFact -= moreWidth;
                    widthFact -= cellSpace;
                }
                if (heigthFact >= 1) {
                    heigthFact -= cellSpace;
                }
                //生成图片对象
                WritableImage image = new WritableImage(cellCol+widthStart, cellRow + heightStart,
                        widthFact, heigthFact, imgFile0.getPngByteArray().toByteArray());
                //将图片对象插入到sheet
                picSheet.addImage(image);
                //开始高度累加，获取下一张图片的起始高度（相对该单元格）
                widthStart += widthFact;
                widthStart +=cellSpace;//图片直接间隔为cellSpace
            }catch (Exception e ){
                System.out.println(e);
                continue;
            }

        }
    }
    //发送响应流方法
    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}